package employee;

import user.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDao {
    private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/enterprise_db";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "hmh061222";
    //新增员工
    public void addEmployee(Employee emp) throws SQLException {
        String sql = "insert into employee(empno,ename,job,mgr,hiredata,sal,ofee,deptno) values (?,?,?,?,?,?,?,?)";
        try (Connection conn = DBUtil.getConn();
             PreparedStatement pstmt = conn.prepareStatement(sql)){
            pstmt.setInt(1,emp.getEmpno());
            pstmt.setString(2,emp.getEname());
            pstmt.setString(3,emp.getJob());
            pstmt.setString(4,emp.getMgr());
            pstmt.setString(5, String.valueOf(new Timestamp(emp.getHiredata().getTime())));
            pstmt.setDouble(6,emp.getSal());
            pstmt.setDouble(7,emp.getOfee());
            pstmt.setInt(8,emp.getDeptno());
            pstmt.executeUpdate();


        }
    }
    //查询所有员工
    public List<Employee> listEmployees() throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "select empno,ename,job,mgr,hiredata,sal,ofee,deptno,user_id from employee";
        try(Connection conn = DBUtil.getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery()) {
            while(rs.next()){
                Employee emp = new Employee(rs.getInt("empno"),
                        rs.getString("ename"),
                        rs.getString("job"),
                        rs.getString("mgr"),
                        rs.getTimestamp("hiredata"),
                        rs.getDouble("sal"),
                        rs.getDouble("ofee"),
                        rs.getInt("deptno"),
                        rs.getInt("user_id"));
                employees.add(emp);
            }


        }
        return employees;
    }

    //根据用户ID查询员工
    public List<Employee> listEmployeesByUserId(int userId) throws SQLException {
        List<Employee> employees = new ArrayList<>();
        String sql = "select empno,ename,job,mgr,hiredata,sal,ofee,deptno,user_id from employee where user_id = ?";
        try(Connection conn = DBUtil.getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1,userId);
            try(ResultSet rs = pstmt.executeQuery()) {
                while(rs.next()){
                    employees.add(new Employee(
                            rs.getInt("empno"),
                            rs.getString("ename"),
                            rs.getString("job"),
                            rs.getString("mgr"),
                            rs.getTimestamp("hiredata"),
                            rs.getDouble("sal"),
                            rs.getDouble("ofee"),
                            rs.getInt("deptno"),
                            rs.getInt("user_id")

                    ));
                }
            }
           return employees;
        }
    }

    //根据工号删除员工
    public void deleteEmployee(int empno) throws SQLException {
        String sql = "delete from employee where empno = ?";
        try(Connection conn = DBUtil.getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1,empno);
            pstmt.executeUpdate();
        }
    }

    //根据工号修改员工
    public void updateEmployee(Employee emp) throws SQLException {
        String sql = "update employee set ename = ?,job = ?,mgr = ?,hirdata = ?,sal = ?,ofee = ?,daptno = ? where empno = ?";
        try(Connection conn = DBUtil.getConn();
            PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1,emp.getEmpno());
            pstmt.setString(2,emp.getEname());
            pstmt.setString(3,emp.getJob());
            pstmt.setString(4,emp.getMgr());
            pstmt.setString(5, String.valueOf(new Timestamp(emp.getHiredata().getTime())));
            pstmt.setDouble(6,emp.getSal());
            pstmt.setDouble(7,emp.getOfee());
            pstmt.setInt(8,emp.getDeptno());
            pstmt.executeUpdate();
        }
    }


}
